package com.yonyou.gmmc.parts.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.yonyou.gmmc.parts.dto.PartDto;

public class ImportExcel {
	private static final Logger logger = Logger.getLogger(ImportExcel.class);
	public static List<PartDto> readExcel(String excelPath) throws IOException, InvalidFormatException {
		List<PartDto> listDto = new ArrayList<PartDto>();
		PartDto pd ;
		
		Pattern pattern = Pattern.compile("[\\s\\\\/:\\*\\?\\\"<>\\|]");
        Matcher matcher = pattern.matcher(excelPath);
        excelPath = matcher.replaceAll(""); // 将匹配到的非法字符以空替换
        excelPath.matches("[^\\s\\\\/:\\*\\?\\\"<>\\|](\\x20|[^\\s\\\\/:\\*\\?\\\"<>\\|])*[^\\s\\\\/:\\*\\?\\\"<>\\|\\.]$"); 
		
        FileInputStream fileIn = new FileInputStream(excelPath);  
		//根据指定的文件输入流导入Excel从而产生Workbook对象  
		Workbook wb0 = WorkbookFactory.create(fileIn);  
		//获取Excel文档中的第一个表单  
		Sheet sht0 = wb0.getSheetAt(0);  
		for(Row row : sht0) {
			//如果当前行的行号（从0开始）未达到2（第三行）则从新循环  
			if(row.getRowNum() < 1) {
				continue;
			}
			pd = new PartDto();
			pd.setPartName(row.getCell(0).getStringCellValue());
			pd.setPartPrice((int)row.getCell(1).getNumericCellValue());
			pd.setPartType(row.getCell(2).getStringCellValue());
			pd.setPartCode(row.getCell(3).getStringCellValue());
			pd.setImg(row.getCell(4).getStringCellValue());
			pd.setCarModels(row.getCell(5).getStringCellValue());
			listDto.add(pd);
		}
		fileIn.close();   
		return listDto;
	}
}
